Use QLDA
go

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DuAn_NhanVien_DuAn]') AND parent_object_id = OBJECT_ID(N'[dbo].[DuAn_NhanVien]'))
ALTER TABLE [dbo].[DuAn_NhanVien] DROP CONSTRAINT [FK_DuAn_NhanVien_DuAn]
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DuAn]') AND type in (N'U'))
DROP TABLE [dbo].[DuAn]

go
Create table DuAn
(
	MaDuAn int not null constraint PK_DuAn Primary key,
	TenDuAn nvarchar(100),
	NgayBatDau datetime, 
	NgayHoanThanh datetime,
	TruongDuAn int
)
go

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DuAn_NhanVien_NhanVien]') AND parent_object_id = OBJECT_ID(N'[dbo].[DuAn_NhanVien]'))
ALTER TABLE [dbo].[DuAn_NhanVien] DROP CONSTRAINT [FK_DuAn_NhanVien_NhanVien]
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NhanVien]') AND type in (N'U'))
DROP TABLE [dbo].[NhanVien]
go

Create table NhanVien
(
	MaNhanVien int not null,
	TenNhanVien nvarchar(30),
	Tuoi int,
	DiaChi nvarchar(500),
	DienThoai varchar(15),
	Email nvarchar(50),
	constraint PK_NhanVien primary key (MaNhanVien)
)
go

Drop table DuAn_NhanVien
go

Create table DuAn_NhanVien
(
	MaDuAn int,
	MaNhanVien int,
	NgayBatDau datetime,
	NgayKetThuc datetime,
	constraint PK_DuAn_NhanVien primary key (MaDuAn, MaNhanVien),
	constraint FK_DuAn_NhanVien_DuAn foreign key (MaDuAn) references DuAn (MaDuAn),
	constraint FK_DuAn_NhanVien_NhanVien foreign key (MaNhanVien) references NhanVien (MaNhanVien)
)


--Sua bang
Alter table NhanVien add DanToc nvarchar (50)
go

alter table NhanVien alter column DanToc nvarchar(500)
go

alter table NhanVien drop column DanToc

Select * from NhanVien

--Xoa bang NhanVien
-->Se bi loi

--Phai xoa khoa ngoai
	alter table DuAn_NhanVien drop constraint FK_DuAn_NhanVien_NhanVien
	Drop table NhanVien
go
--Tao lai bang nhan vien
Create table NhanVien
(
	MaNhanVien int not null,
	TenNhanVien nvarchar(30),
	Tuoi int,
	DiaChi nvarchar(500),
	DienThoai varchar(15),
	Email nvarchar(50),
	constraint PK_NhanVien primary key (MaNhanVien)
)
go

alter table Duan_NhanVien add constraint FK_Duan_NhanVien_NhanVien foreign key
(MaNhanVien) references NhanVien(MaNhanVien)

alter table Duan add Constraint FK_DuAn_TruongDuAn foreign key 
(TruongDuAn) references NhanVien (MaNhanVien)

Select * from NhanVien

Insert into NhanVien
(MaNhanVien,TenNhanVien,Tuoi,DiaChi,DienThoai,Email)
values
(1,N'Nguyen Van A',15,N'Phuong Mai','0914868686','abc@gmail.com')

Insert into NhanVien
(MaNhanVien,TenNhanVien,Tuoi,DiaChi,DienThoai,Email)
values
(2,N'Nguyen Van B',15,N'Phuong Mai','0914868686','abc@gmail.com')

Insert into DuAn
(MaDuAn, TenDuAn, NgayBatDau, NgayHoanThanh, TruongDuAn)
values
(2,'A','12/12/2010','12/30/2010',1)

Select * from DuAn

Update NhanVien set DienThoai = '043868686'
where MaNhanVien = 2
Select * from NhanVien


ALTER TABLE dbo.DuAn 
alter column NgayBatDau datetime

Alter table dbo.DuAn drop constraint DF_DuAn_NgayBatDau
go

-------------------------------------THEM SUA XOA RANG BUOC
ALTER TABLE dbo.DuAn 
ADD CONSTRAINT
	DF_DuAn_NgayBatDau DEFAULT getdate() FOR NgayBatDau
GO

ALTER TABLE dbo.DuAn
Add Constraint 
	CK_DuAn_NgayHoanThanh CHECK (NgayHoanThanh>=NgayBatDau)
go

--Insert du lieu de kiem tra
Delete DuAn where MaDuAn = 5
Insert into DuAn
(MaDuAn, TenDuAn, NgayBatDau, NgayHoanThanh, TruongDuAn)
values
(5,'A','12/30/2010','12/22/2010',1)
--Chen bao loi
--Xong

Select * from DuAn
--Xoa Constraint xong, chen lai
alter table dbo.DuAn Drop constraint CK_DuAn_NgayHoanThanh
go
Insert into DuAn
(MaDuAn, TenDuAn, NgayBatDau, NgayHoanThanh, TruongDuAn)
values
(5,'A','12/30/2010','12/22/2010',1)
go
Select * from DuAn
ALTER TABLE dbo.DuAn with nocheck
Add Constraint 
	CK_DuAn_NgayHoanThanh CHECK (NgayHoanThanh>NgayBatDau)
go

Insert into DuAn
(MaDuAn, TenDuAn, NgayBatDau, NgayHoanThanh, TruongDuAn)
values
(6,'A','12/30/2010','12/22/2010',1)

Update DuAn
Set NgayHoanThanh = '12/23/2010' where MaDuAn = 5

Update DuAn
Set NgayHoanThanh = '12/31/2010' where MaDuAn = 5

--With NoCheck co the dat truoc cac loai constrain
--Vi du: Check, Foreignkey

--Xoa khoa ngoai giua nhan vien va du an
alter table Duan drop constraint FK_Duan_TruongDuAn
--Chen vao bang du an mot ong truong du an voi ma chua co trong bang nhan vien
Insert into duan (MaDuAn, TenDuAn, NgayBatDau, NgayHoanThanh, TruongDuAn)
values (10,'F','12/12/2010','12/14/2010',13)
--Thiet lap lai quan he khoa ngoai giuaa nhan vien va du an
--> Co van de: Nhan vien 13 chua ton tai trong bang NhanVien
--> Dung With NoCheck de van them duoc rang buoc nay

Select * from NhanVien
Select * FRom duan
-->loi
alter table Duan add Constraint FK_DuAn_TruongDuAn foreign key (TruongDuAn)
references NhanVien(MaNhanVien)
-->Bo qua, van cho tao --> With Nocheck
alter table Duan 
With NoCheck
add Constraint FK_DuAn_TruongDuAn foreign key (TruongDuAn)
references NhanVien(MaNhanVien)

--Ke thu sau khi co rang buoc khoa ngoai, truong du an phai lay tu bang nhan vien
Insert into duan (MaDuAn, TenDuAn, NgayBatDau, NgayHoanThanh, TruongDuAn)
values (11,'F','12/12/2010','12/14/2010',14)